package com.TEAM6.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import com.TEAM6.beans.BookInfo;
import com.TEAM6.beans.MemberBean;
import com.TEAM6.beans.NoticeInfo;


public class MemberDAO {
	private Connection  road;
	private PreparedStatement car;
	private ResultSet results;
	private DataSource ds;
	
	public MemberDAO(){
		// DBCP에 connect 요청
		try{
			Context init = new InitialContext();
			ds = (DataSource)init.lookup("java:comp/env/SJHDB");
		}
		catch(Exception e){
			e.printStackTrace();
			return;
		}
	}
	
	public boolean isMember(MemberBean member){
		boolean present = false;
		String sql = "SELECT COUNT(*) FROM MEMBERS WHERE MM_ID=? AND MM_PASSWORD=?";
		
		try{
			road = ds.getConnection();
			
			car = road.prepareStatement(sql);
			car.setString(1, member.getId());
			car.setString(2, member.getPassword());
			
			results = car.executeQuery();
			while(results.next()){
				if(results.getInt(1)!=0){present=true;}
			}
			
		}
		catch(Exception e){e.printStackTrace();}
		finally{
			if(results != null)try{results.close();}catch(SQLException e){}
			if(car != null)try{car.close();}catch(SQLException e){}
			if(road != null)try{road.close();}catch(SQLException e){}
		}
		return present;
	}
	
	public String getMemberName(String id){
		String present = null;
		String sql = "SELECT MM_NAME FROM MM WHERE MM_ID=?";
		
		try{
			road = ds.getConnection();
			
			car = road.prepareStatement(sql);
			car.setString(1, id);
			
			results = car.executeQuery();
			while(results.next()){
				present = results.getNString(1);
			}
			
		}
		catch(Exception e){e.printStackTrace();}
		finally{
			if(results != null)try{results.close();}catch(SQLException e){}
			if(car != null)try{car.close();}catch(SQLException e){}
			if(road != null)try{road.close();}catch(SQLException e){}
		}
		return present;
	}
	
	public boolean joinMember(MemberBean member){
		boolean present = false;
		String sql = "INSERT INTO MEMBERS(MM_ID, MM_PASSWORD, MM_NAME, MM_PHONE) "
				+ "VALUES(?,?,?,?)";
		
		try{
			road = ds.getConnection();
			road.setAutoCommit(false);
			
			car = road.prepareStatement(sql);
			
			car.setString(1, member.getId());
			car.setString(2, member.getPassword());
			car.setString(3, member.getName());
			car.setString(4, member.getphone());
			
			int cnt = car.executeUpdate();
			
			if(cnt != 0) present = true;
			road.commit();
		}
		catch(Exception e){
			try{road.rollback(); e.printStackTrace();}catch(Exception ex){}
		}
		finally{
			if(results != null)try{results.close();}catch(SQLException e){}
			if(car != null)try{car.close();}catch(SQLException e){}
			if(road != null)try{road.close();}catch(SQLException e){}
		}
		return present;
	}
	
	
	public ArrayList<BookInfo> rentalInfo(String id){

		ArrayList<BookInfo> list = new ArrayList<BookInfo>() ;
		String sql = "SELECT ISBN, LNAME, NAME, RDATE, RETURNDATE FROM RENTALINFO WHERE MID=?";		
		try{
			road = ds.getConnection();
			
			car = road.prepareStatement(sql);
			car.setString(1,id);
			
			results = car.executeQuery();
			while(results.next()){
				BookInfo e = new BookInfo();
				
				e.setIsbn(results.getNString(1));
				e.setLname(results.getNString(2));
				e.setName(results.getNString(3));
				e.setRentaldate(results.getDate(4));
				e.setReturndate(results.getDate(5));
				
				
				list.add(e);
			}
			
		}
		catch(Exception e){e.printStackTrace();}
		finally{
			if(results != null)try{results.close();}catch(SQLException e){}
			if(car != null)try{car.close();}catch(SQLException e){}
			if(road != null)try{road.close();}catch(SQLException e){}
		}
		return list;
	}
	public ArrayList<BookInfo> delayInfo(String id){

		ArrayList<BookInfo> list = new ArrayList<BookInfo>() ;
		String sql = "SELECT ISBN, LNAME, NAME, RENTALDATE, RETURNDATE, DELAYDAYS FROM DELAYINFO WHERE MID=?";
		
		try{
			road = ds.getConnection();
			
			car = road.prepareStatement(sql);
			car.setString(1,id);
			
			results = car.executeQuery();
			while(results.next()){
				BookInfo e = new BookInfo();
				
				e.setIsbn(results.getNString(1));
				e.setLname(results.getNString(2));
				e.setName(results.getNString(3));
				e.setRentaldate(results.getDate(4));
				e.setReturndate(results.getDate(5));
				e.setdelaydays(results.getInt(6));
				
				list.add(e);
			}
			
		}
		catch(Exception e){e.printStackTrace();}
		finally{
			if(results != null)try{results.close();}catch(SQLException e){}
			if(car != null)try{car.close();}catch(SQLException e){}
			if(road != null)try{road.close();}catch(SQLException e){}
		}
		return list;
	}
	
	public ArrayList<BookInfo> bookingInfo(String id){

		ArrayList<BookInfo> list = new ArrayList<BookInfo>() ;
		String sql = "SELECT ISBN, LNAME, NAME, BOOKINGDATE FROM BOOKINGINFO WHERE MID=?";
		
		try{
			road = ds.getConnection();
			
			car = road.prepareStatement(sql);
			car.setString(1,id);
			
			results = car.executeQuery();
			while(results.next()){
				BookInfo e = new BookInfo();
				
				e.setIsbn(results.getNString(1));
				e.setLname(results.getNString(2));
				e.setName(results.getNString(3));
				e.setBookingdate(results.getDate(4));
				
				list.add(e);
			}
			
		}
		catch(Exception e){e.printStackTrace();}
		finally{
			if(results != null)try{results.close();}catch(SQLException e){}
			if(car != null)try{car.close();}catch(SQLException e){}
			if(road != null)try{road.close();}catch(SQLException e){}
		}
		return list;
	}
	
	public ArrayList<NoticeInfo> NoticeInfo(){

		ArrayList<NoticeInfo> list = new ArrayList<NoticeInfo>() ;
		String sql = "SELECT RNUM,NUM,TITLE,CONTEXT,WRITER,B_DATE,TYPE,FIL FROM NOTICEINFO";
		
		try{
			road = ds.getConnection();
			
			car = road.prepareStatement(sql);
			
			
			results = car.executeQuery();
			while(results.next()){
				NoticeInfo e = new NoticeInfo();
				
				e.setNumber(results.getInt(2));
				e.setTitle(results.getNString(3));
				e.setContext(results.getNString(4));
				e.setWriter(results.getNString(5));
				e.setDate(results.getDate(6));
				e.setType(results.getNString(7));
				e.setFile(results.getNString(8));
				list.add(e);
			}
			
		}
		catch(Exception e){e.printStackTrace();}
		finally{
			if(results != null)try{results.close();}catch(SQLException e){}
			if(car != null)try{car.close();}catch(SQLException e){}
			if(road != null)try{road.close();}catch(SQLException e){}
		}
		return list;
	}
	public ArrayList<NoticeInfo> FreeBoardInfo(){

		ArrayList<NoticeInfo> list = new ArrayList<NoticeInfo>() ;
		String sql = "SELECT RNUM,NUM,TITLE,CONTEXT,WRITER,B_DATE,TYPE,FIL FROM FREEBOARDINFO";
		
		try{
			road = ds.getConnection();
			
			car = road.prepareStatement(sql);
			
			
			results = car.executeQuery();
			while(results.next()){
				NoticeInfo e = new NoticeInfo();
				
				e.setNumber(results.getInt(2));
				e.setTitle(results.getNString(3));
				e.setContext(results.getNString(4));
				e.setWriter(results.getNString(5));
				e.setDate(results.getDate(6));
				e.setType(results.getNString(7));
				e.setFile(results.getNString(8));
				list.add(e);
			}
			
		}
		catch(Exception e){e.printStackTrace();}
		finally{
			if(results != null)try{results.close();}catch(SQLException e){}
			if(car != null)try{car.close();}catch(SQLException e){}
			if(road != null)try{road.close();}catch(SQLException e){}
		}
		return list;
	}
	
}

/* 
    try{
			
		}
		catch(Exception e){e.printStackTrace();}
		finally{
			if(results != null)try{results.close();}catch(SQLException e){}
			if(car != null)try{car.close();}catch(SQLException e){}
			if(road != null)try{road.close();}catch(SQLException e){}
		}
 * */
